package com.bsoft.gol.hcs.satisfyStatic.bean.vo;

import ctd.controller.exception.ControllerException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.bsoft.gol.hcs.satisfyStatic.bean.qo.QuerySatisfySurveyStatisticListQo;
import com.bsoft.gol.hcs.utils.CommonUtils;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.List;

/**
 * @description: 统计报表excel返回
 * @author: chenJij@bsoft.com.cn
 * @date: 2022/8/1 9:27
 */
public class SummarySatisfyStatisticExcelUtil {

    private final static Logger logger = LoggerFactory.getLogger(SummarySatisfyStatisticExcelUtil.class);

    private static CellStyle style;

    /**
     * 生成汇总Excel
     * @throws NullPointerException
     */
    public static byte[] getExcelAsByteArray(QuerySatisfySurveyStatisticListQo qo,List<String> titleList, List<QuerySatisfySurveyStatisticListVo> dataList) throws ControllerException {
        if (titleList == null) {
            throw new ControllerException("excel标题为空");
        }
        Workbook wb;
        ByteArrayOutputStream out = new ByteArrayOutputStream(2048);
        try {
            wb = new XSSFWorkbook();
            Sheet sheet = wb.createSheet("科室统计");

            initCellStyle(wb);
            initHeadStyle(sheet,titleList);
            initHeadContent(titleList,sheet);
            initBody(qo,titleList,dataList, sheet);

            wb.write(out);
        } catch (IOException e) {
            logger.error(e.getMessage(), e);
        }
        return out.toByteArray();
    }

    private static void initBody(QuerySatisfySurveyStatisticListQo qo,List<String> titleList,List<QuerySatisfySurveyStatisticListVo> dataList, Sheet sheet) {
        if(CommonUtils.isNotEmpty(dataList)){
            for (int i = 0; i < dataList.size(); i++) {
                Row row = sheet.createRow(i + 3);
                QuerySatisfySurveyStatisticListVo summary = dataList.get(i);
                setCellValue(row, 0, style, summary.getSort());
                setCellValue(row, 1, style, CommonUtils.isEmpty(summary.getTypeName()) ? "" : summary.getTypeName());
                setCellValue(row, 2, style, summary.getProportion());
                setCellValue(row, 3, style, summary.getSurveyPersonNumber());
                setCellValue(row, 4, style, summary.getPlanNumber());
                setCellValue(row, 5, style, summary.getAfterHosNumber());
                setCellValue(row, 6, style, summary.getScanCodeNumber());
                setCellValue(row, 7, style, summary.getAbnormalNumber());
                setCellValue(row, 8, style, summary.getAbnormalProportion());
                if ("0".equals(qo.getStatisticExtraId())) {
                    setCellValue(row, 9, style, summary.getScore1());
                    setCellValue(row, 10, style, summary.getStandardDeviationScore1());
                    setCellValue(row, 11, style, summary.getMedian());
                    setCellValue(row, 12, style, summary.getMaxScore());
                    setCellValue(row, 13, style, summary.getMinScore());
                } else {
                    notNullSetCellValue(row, 9,9, titleList, style, summary.getScore1());
                    notNullSetCellValue(row, 10,9,  titleList, style, summary.getStandardDeviationScore1());
                    notNullSetCellValue(row, 11,10,  titleList, style, summary.getScore2());
                    notNullSetCellValue(row, 12,10,  titleList, style, summary.getStandardDeviationScore2());
                    notNullSetCellValue(row, 13,11,  titleList, style, summary.getScore3());
                    notNullSetCellValue(row, 14,11,  titleList, style, summary.getStandardDeviationScore3());
                    notNullSetCellValue(row, 15,12,  titleList, style, summary.getScore4());
                    notNullSetCellValue(row, 16,12,  titleList, style, summary.getStandardDeviationScore4());
                    notNullSetCellValue(row, 17,13,  titleList, style, summary.getScore5());
                    notNullSetCellValue(row, 18,13,  titleList, style, summary.getStandardDeviationScore5());
                    notNullSetCellValue(row, 19,14,  titleList, style, summary.getScore6());
                    notNullSetCellValue(row, 20,14,  titleList, style, summary.getStandardDeviationScore6());
                    notNullSetCellValue(row, 21,15, titleList, style, summary.getScore7());
                    notNullSetCellValue(row, 22,15,  titleList, style, summary.getStandardDeviationScore7());
                    notNullSetCellValue(row, 23,16,  titleList, style, summary.getScore8());
                    notNullSetCellValue(row, 24,16,  titleList, style, summary.getStandardDeviationScore8());
                    notNullSetCellValue(row, 25,17,  titleList, style, summary.getScore9());
                    notNullSetCellValue(row, 26,17,  titleList, style, summary.getStandardDeviationScore9());
                    notNullSetCellValue(row, 27,18,  titleList, style, summary.getScore10());
                    notNullSetCellValue(row, 28,18,  titleList, style, summary.getStandardDeviationScore10());
                    notNullSetCellValue(row, 29,19,  titleList, style, summary.getScore11());
                    notNullSetCellValue(row, 30,19,  titleList, style, summary.getStandardDeviationScore11());
                    notNullSetCellValue(row, 31,20,  titleList, style, summary.getScore12());
                    notNullSetCellValue(row, 32,20,  titleList, style, summary.getStandardDeviationScore12());
                }

            }
        }

    }

    private static void notNullSetCellValue(Row row, int column, int sort, List<String> titleList, CellStyle style, String value) {
        if (titleList.size() >= (sort + 1)) {
            Cell cell = row.createCell(column);
            cell.setCellStyle(style);
            cell.setCellValue(value);
        }
    }

    private static String numChangeString(Double num){
        DecimalFormat df = new DecimalFormat("0.00");//格式化小数，不足的补0
        String numStr = df.format(num);//返回的是String类型的
        return numStr;
    }

    private static void initHeadContent(List<String> titleList, Sheet sheet) {
        Row row0 = sheet.createRow(0);
        Row row2 = sheet.createRow(2);
        Row row3 = sheet.createRow(3);
        String title= "满意度统计表";

        setCellValue(row0, 0, style, title);

        for (int i = 0; i <titleList.size() ; i++) {
            String headerName = titleList.get(i);
            setCellValue(row2, i, style, headerName);
        }
    }

    private static void initHeadStyle(Sheet sheet,List<String> titleList) {
        sheet.setColumnWidth(0, 15 * 256); // 设置宽度

        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, titleList.size()-1));


    }

    private static void setCellValue(Row row, int column, CellStyle style, String value) {
        Cell cell = row.createCell(column);
        cell.setCellStyle(style);
        cell.setCellValue(value);
    }

    private static void initCellStyle(Workbook wk) {
        style = wk.createCellStyle();
//        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直
//        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平
        style.setWrapText(true); //自动换行
    }

    public static void main(String[] args) throws IOException {
//        List<QueryDeptConsultStatisticsListVo.DataBean> list = new ArrayList<>();
//        QuerySatisfySurveyStatisticListQo qo = new QuerySatisfySurveyStatisticListQo();
//        qo.setStatisticExtraId("0");
//        List<String> titleList = new ArrayList<>();
//        titleList.add("请求头1")；
//        byte[] data = getExcelAsByteArray(list,"1","1","111");
//        OutputStream out = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\test112.xlsx");
//        out.write(data, 0, data.length);
    }
}
